/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Mapper;

import java.sql.*;
import java.util.*;
import java.sql.PreparedStatement;
import Bean.BookBean;
/**
 *
 * @author Quang Thao
 */
public class BookMapper extends DBMapper {

    public BookMapper() throws SQLException, Exception {
        super();
    }

    public List GetAll() throws Exception {
        List list = new ArrayList<Bean.BookBean>();
        PreparedStatement stmt = getConnection().prepareStatement("SELECT * FROM Book");
        ResultSet rs = stmt.executeQuery();
        if (rs != null) {
            while (rs.next()) {
                Bean.BookBean book = new Bean.BookBean();
                book.setISBN(rs.getString(1));
                book.setName(rs.getString(2));
                // Type
                BO.Book_TypeBO btBO = new BO.Book_TypeBO();
                Bean.Book_TypeBean bt = new Bean.Book_TypeBean();
                bt = btBO.GetInfo(rs.getString(3));
                book.setType(bt);

                book.setDescribe(rs.getString(4));
                BO.PublisherBO pbBO = new BO.PublisherBO();
                Bean.PublisherBean pb = new Bean.PublisherBean();
                pb = pbBO.GetInfo(rs.getString(5));
                book.setPublisher(pb);

                int year = Integer.parseInt(rs.getString(6));
                book.setYear(year);
                double cost = Double.parseDouble(rs.getString(7));
                book.setCost(cost);
                int quantity = Integer.parseInt(rs.getString(8));
                book.setQuantity(quantity);
                book.setAuthor(rs.getString(9));
                book.setPicture(rs.getString(10));
                list.add(book);
            }
        }
        stmt.close();
        return list;
    }

    public List GetAll(String type) throws Exception {
        List list = new ArrayList<Bean.BookBean>();
        PreparedStatement stmt = getConnection().prepareStatement("SELECT * FROM Book WHERE Type='" + type + "'");
        ResultSet rs = stmt.executeQuery();
        if (rs != null) {
            while (rs.next()) {
                Bean.BookBean book = new Bean.BookBean();
                book.setISBN(rs.getString(1));
                book.setName(rs.getString(2));
                // Type
                BO.Book_TypeBO btBO = new BO.Book_TypeBO();
                Bean.Book_TypeBean bt = new Bean.Book_TypeBean();
                bt = btBO.GetInfo(rs.getString(3));
                book.setType(bt);

                book.setDescribe(rs.getString(4));
                book.getPublisher().setID(rs.getString(5));
                int year = Integer.parseInt(rs.getString(6));
                book.setYear(year);
                double cost = Double.parseDouble(rs.getString(7));
                book.setCost(cost);
                int quantity = Integer.parseInt(rs.getString(8));
                book.setQuantity(quantity);
                book.setAuthor(rs.getString(9));
                book.setPicture(rs.getString(10));
                list.add(book);
            }
        }
        stmt.close();
        return list;
    }

    public Bean.BookBean GetInfo(String isbn) throws Exception {
        Bean.BookBean book = null;
        PreparedStatement stmt = getConnection().prepareStatement("SELECT * FROM Book WHERE ISBN='" + isbn + "'");
        ResultSet rs = stmt.executeQuery();
        if (rs != null && rs.next()) {
            book = new Bean.BookBean();
            book.setISBN(rs.getString(1));
            book.setName(rs.getString(2));
            // Type
            BO.Book_TypeBO btBO = new BO.Book_TypeBO();
            Bean.Book_TypeBean bt = new Bean.Book_TypeBean();
            bt = btBO.GetInfo(rs.getString(3));
            book.setType(bt);
            
            BO.PublisherBO pbBO = new BO.PublisherBO();
            Bean.PublisherBean pb = new Bean.PublisherBean();
            pb = pbBO.GetInfo(rs.getString(5));
            book.setPublisher(pb);
            book.setDescribe(rs.getString(4));
            int year = Integer.parseInt(rs.getString(6));
            book.setYear(year);
            double cost = Double.parseDouble(rs.getString(7));
            book.setCost(cost);
            int quantity = Integer.parseInt(rs.getString(8));
            book.setQuantity(quantity);
            book.setAuthor(rs.getString(9));
            book.setPicture(rs.getString(10));
        }
        stmt.close();
        return book;
    }

    public List GetListBySpecial(String special) throws Exception {
        List list = new ArrayList<Bean.BookBean>();
        PreparedStatement stmt = getConnection().prepareStatement("SELECT b.ISBN,b.BookName,b.Type,b.Describe,b.Publisher,b.YearPublish"
                + ",b.Cost,b.Quantity,b.Author,b.Picture "
                + "FROM Book b, Book_Special bp, Book_State bs "
                + "WHERE b.ISBN=bp.ISBN AND bp.State=bs.ID AND bs.ID='" + special + "'");
        ResultSet rs = stmt.executeQuery();
        if (rs != null) {
            while (rs.next()) {
                Bean.BookBean book = new Bean.BookBean();
                book.setISBN(rs.getString(1));
                book.setName(rs.getString(2));
                // Type
                BO.Book_TypeBO btBO = new BO.Book_TypeBO();
                Bean.Book_TypeBean bt = new Bean.Book_TypeBean();
                bt = btBO.GetInfo(rs.getString(3));
                book.setType(bt);

                book.setDescribe(rs.getString(4));
                book.getPublisher().setID(rs.getString(5));
                int year = Integer.parseInt(rs.getString(6));
                book.setYear(year);
                double cost = Double.parseDouble(rs.getString(7));
                book.setCost(cost);
                int quantity = Integer.parseInt(rs.getString(8));
                book.setQuantity(quantity);
                book.setAuthor(rs.getString(9));
                book.setPicture(rs.getString(10));
                list.add(book);
            }
        }
        stmt.close();
        return list;
    }
       public boolean IsExist(Bean.BookBean bk) throws Exception {
        PreparedStatement stmt = getConnection().prepareStatement("SELECT * FROM Book Where ISBN='" + bk.getISBN()
                + "'");
        ResultSet rs = stmt.executeQuery();
        if (rs.next()) {
            stmt.close();
            return true;
        }
        stmt.close();
        return false;
    }
       public int Add(Bean.BookBean bk) throws Exception {
        PreparedStatement stmt = getConnection().prepareStatement("Insert Into Book Values('"
                + bk.getISBN() + "','" + bk.getName() + "','"+ bk.getType().getID() +"','"
                + bk.getDescribe() + "','" + bk.getPublisher().getID() + "'," + bk.getYear() + ","
                + bk.getCost() + "," + bk.getQuantity() + ",'"+ bk.getAuthor() + "','" + bk.getPicture() + "')");
        int found = stmt.executeUpdate();
        stmt.close();
        return found;
    }
       public int Delete(BookBean bk) throws Exception {
        PreparedStatement stmt = getConnection().prepareStatement("Delete From Book Where ISBN='" + bk.getISBN() + "'");
        int found = stmt.executeUpdate();
        stmt.close();
        return found;
    }
}
